﻿USE [Gestion2]
GO

/****** Object:  StoredProcedure [dbo].[WASP_D0008_CREA_BASE]    Script Date: 11/22/2011 16:09:01 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WASP_D0008_CREACION_BASE]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WASP_D0008_CREA_BASE]
GO

USE [Gestion2]
GO

/****** Object:  StoredProcedure [dbo].[WASP_D0008_CREA_BASE]    Script Date: 11/22/2011 16:09:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[WASP_D0008_CREA_BASE] --EN DTS SE LLAMA CREACION BASE
(
@fecha_inicio as char(8)
)
AS
BEGIN

      select distinct
      aosuc as suc,
       substring(pfndoc, 1, len(pfndoc)-1) as RUT,
       substring(pfndoc, len(pfndoc),1 ) as DV,
       rtrim(ltrim(pfape1)) +' '+
       rtrim(ltrim(pfape2)) +' '+
       pfnom1 as NOMBRE,
	aooper as NRO_CREDITO,
	Aofval as INICIO_COBERTURA, 
	dateadd(day, -1, dateadd(year,1, aofval)) as FIN_COBERTURA,
	null as NRO_CUOTAS,
	Dotelfp as TLFONOPEN,
	rtrim(docallp) +' '+ rtrim(donrop) DIRTRABAJA ,
	z07310com as comuna
	
			
into spt_temp
from 
	--fsd611 p (nolock), 
	fsd611 p (nolock) Left Join z07331 z (nolock)on p.ppcta = z.Z07331CTA,
	fsd010 (nolock),
	--fsr008 (nolock), 
	fsd002 (nolock),
	--fsr005 (nolock), 
	--fsd005 (nolock), 
	--z07331 (nolock), 
	jt73109 (nolock), 
	fsd601 b (nolock),
	fsr008 a (nolock) Left Join fsr005 r (nolock) on a.pendoc = r.pendoc and r.doordp = 1,
	fsr008 x (nolock) Left Join fsd005 d (nolock) on x.pendoc = d.pendoc

where     (p.ppimp11 = 104 or
		   p.ppimp12 = 104 or
	       p.ppimp13 = 104 or
           p.ppimp14 = 104 or
           p.ppimp15 = 104 or
           p.ppimp16 = 104 or
           p.ppimp17 = 104 or
           p.ppimp18 = 104 or
           p.ppimp19 = 104 ) and
           p.ppcta = aocta and
           aosuc = p.ppsuc and
           aooper = p.ppoper and
           aosbop = 0 and
           jt73109fpa = aooper  and 
           jt73109suc = aosuc and
           --jt73109FAP = convert(varchar, DATEADD( day , -1, getdate()), 112) and
		jt73109FAP = @fecha_inicio and
    --jt73109FAP = '?' and
	   aocta = a.ctnro and
	   --fsr008.pendoc = pfndoc and
	   a.pendoc = pfndoc and
	   --fsr008.pendoc *= fsr005.pendoc and fsr005.doordp = 1 and
	   --fsr008.pendoc *= fsd005.pendoc and
	   --p.ppcta *= Z07331CTA and
	   p.pgcod = b.pgcod and
	   p.ppmod = b.ppmod and
	   p.ppsuc = b.ppsuc and 
	   p.ppmda = b.ppmda and
	   p.ppcta = b.ppcta and 
	   p.ppoper = b.ppoper and
	   p.ppsbop = b.ppsbop and
	   p.pptope = b.pptope and
	   b.d601co = 'S' and 
	   p.pptipo = '' and
           p.ppexte = 9 and
                           NOT EXISTS
                           (SELECT *
                            FROM as_desistidos as anu
                            WHERE aocta  = anu.hcta and
              		   aooper = anu.hoper and
                                           aosuc = anu.hsucur )
END

GO
                                           